import { Injectable } from "@nestjs/common";
import * as mysql from "mysql";


@Injectable()
export class DBReaderRepository {
    private readonly pool: mysql.Pool;
    constructor(

    ) {
        this.pool = mysql.createPool({
            host: "huafeiiot-extranet.mysql.rds.aliyuncs.com",
            user: "lintao_readonly",
            password: "lintao_readonly123",
            port: 1000,
            database: "db_hfms",

        });
    }

    private query<T>(sql: string, params?: any[]): Promise<T> {
        // console.log(this.pool)
        // if(this.pool.isActive)

        return new Promise((resolve, reject) => {
            this.pool.getConnection((error, connection) => {
                if (error) {
                    reject(error);
                } else {
                    const { sql: _sql } = connection.query(sql, params, (error, result) => {
                        if (error) {
                            reject(error);
                        } else {
                            resolve(result)
                        }
                        //需要释放掉, 不然后面连接会超时
                        connection.release();
                        // console.log(_sql);

                    });
                }
            })
        });
    }


    async test() {
        const sql = `select * from rest_user`;
        const res: any = await this.query(sql);
        console.log(res);
        return res;
    }

    // hotelRoomData=hotel_room_card;//本地//获取酒店id、酒店名和房间对应register
    async insert_card_for_power_data(leftTime, rightTime) {
        // console.log(leftTime);
        // console.log(rightTime);
        const sql = `SELECT registerId,MAX(CASE STATUS WHEN 0 THEN acount ELSE 0 END ) invalid,SUM(acount) sumcount FROM (
            SELECT registerId,STATUS,COUNT(*) acount FROM rest_net_using_inspection WHERE reqTime>? AND resTime<?  GROUP BY registerId,STATUS
            ) rnui GROUP BY registerId`;//以防万一，排序

        let params;
        params = [leftTime, rightTime];

        const res: any = await this.query(sql, params);
        // console.log(res);
        return res;

    }
    //获取所有酒店id，酒店名，房间名，房间对应的registerId，耗时4S，月更新。或根据酒店名Id查找
    async getHotelRoomDataOne() {
        var sql = `SELECT customerId,NAME roomname,registerId,rest_netId FROM \`case\` INNER JOIN (
            SELECT caseId,rnur.id registerId FROM case_node cn INNER JOIN rest_net_using_register rnur ON cn.ieeeAddr=rnur.rest_nodeIeeeAddr
            ) cr ON case.id=cr.caseId order by customerId`;

        const res: any = await this.query(sql);
        return res;
    }

    async getHotelRoomDataTwo(hotel) {
        var sql = `SELECT hotel.caseCustomerId customerId,hr.name roomname,hotel.id,hotel.name hotelname FROM hotel INNER JOIN hotel_room hr ON hotel.id=hr.hotelId`;
        if (hotel != null && hotel != -1) {
            sql += ` WHERE hotel.id IN (?) order by customerId`;
        } else {
            sql += ` order by customerId`;
        }
        let params;
        params = [hotel];

        const res: any = await this.query(sql, params);
        return res;
    }

    async get_hotel_daily_settel(leftTime, rightTime) {//根据时间段查询酒店日租信息
        // console.log(leftTime);
        // console.log(rightTime);
        const sql = `select hotelId,name,roomnum,hds.from,hds.desc,amount,isSettled,isNotified 
                    from hotel_daily_settlement hds 
                    inner join (
                        SELECT hotel.id,hotel.name,COUNT(*) roomnum 
                        FROM hotel INNER JOIN hotel_room ON hotel.id=hotel_room.hotelId 
                        where hotel.id<>9 GROUP BY hotel.id
                    ) h on hds.hotelId=h.id
                    where hds.from>? and hds.from<?
                    order by hotelId`;

        let params;
        params = [leftTime, rightTime];

        const res: any = await this.query(sql, params);
        // console.log(res);
        return res;
    }
    ////合同内期酒店房间数
    async getRenthotelroomnum(date) {
        const sql = `SELECT COUNT(NAME) num 
                    FROM hotel_room
                    INNER JOIN (
                        SELECT hotelId
                        FROM hotel_rent
                        WHERE gmtCreate<? AND rentDeadline>? ) hr ON hotel_room.hotelId=hr.hotelId`;

        let params;
        params = [date, date];

        const res: any = await this.query(sql, params);
        // console.log(res);
        return res;

    }

    async getRenthotelnum(date) {
        const sql = `SELECT COUNT(*) num FROM hotel_rent WHERE gmtCreate<? AND rentDeadline>?`;//合同内酒店数
        let params;
        params = [date, date];
        const res: any = await this.query(sql, params);
        // console.log(res);
        return res;
    }

    async getHotelnum() {
        const sql = `SELECT COUNT(*) num FROM hotel`;//总酒店数
        const res: any = await this.query(sql);
        // console.log(res);
        return res;
    }

    async install_room(leftTime, rightTime) {
        const sql = `SELECT hrr.from,COUNT(*) installnum 
        FROM hotel_rent_room hrr
        WHERE hrr.from>? AND hrr.from<?
        GROUP BY hrr.from
        ORDER BY hrr.from`;
        let params;
        params = [leftTime, rightTime];

        const res: any = await this.query(sql, params);
        // console.log(res);
        return res;
    }




}